package org.smart4j.chapter1.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.smart4j.chapter1.entity.CmsReport;

import java.io.FileOutputStream;

import static java.lang.System.out;

/**
 * Created by ly on 2018/6/27.
 */
public class ExportExcel {

    /**
     * 单条信息导出举报信息Excel
     * @param report
     * @param fout
     */
    public void getValue(CmsReport report, FileOutputStream fout){
        try{
            //1.创建工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //1.1创建合并单元格对象
            CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,5);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress1 = new CellRangeAddress(10,12,0,5);//起始行,结束行,起始列,结束列 被举报人信息
            CellRangeAddress reportContent = new CellRangeAddress(22,24,0,5);//起始行,结束行,起始列,结束列 被举报人信息

            //姓名与身份证号start
            CellRangeAddress callRangeAddress20 = new CellRangeAddress(1,3,0,0);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress21 = new CellRangeAddress(1,3,1,2);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress22 = new CellRangeAddress(1,3,3,3);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress23 = new CellRangeAddress(1,3,4,5);//起始行,结束行,起始列,结束列
            //姓名与身份证号end
            //政治面貌和联系方式start
            CellRangeAddress callRangeAddress30 = new CellRangeAddress(4,6,0,0);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress31 = new CellRangeAddress(4,6,1,2);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress32 = new CellRangeAddress(4,6,3,3);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress33 = new CellRangeAddress(4,6,4,5);//起始行,结束行,起始列,结束列
            //政治面貌和联系方式end

            //單位地址或級別start
            CellRangeAddress callRangeAddress40 = new CellRangeAddress(7,9,0,0);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress41 = new CellRangeAddress(7,9,1,2);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress42 = new CellRangeAddress(7,9,3,3);//起始行,结束行,起始列,结束列
            CellRangeAddress callRangeAddress43 = new CellRangeAddress(7,9,4,5);//起始行,结束行,起始列,结束列
            //單位地址或級別end

            //被举报人 姓名、单位 开始
            CellRangeAddress reported = new CellRangeAddress(13,15,0,0);//起始行,结束行,起始列,结束列
            CellRangeAddress reported1 = new CellRangeAddress(13,15,1,2);//起始行,结束行,起始列,结束列
            CellRangeAddress reported2 = new CellRangeAddress(13,15,3,3);//起始行,结束行,起始列,结束列
            CellRangeAddress reported3 = new CellRangeAddress(13,15,4,5);//起始行,结束行,起始列,结束列

            //被举报人 姓名、单位 结束

            //被举报人职务和所在地区start
            CellRangeAddress reportedAddress = new CellRangeAddress(16,18,0,0);//起始行,结束行,起始列,结束列
            CellRangeAddress reportedAddress1 = new CellRangeAddress(16,18,1,2);//起始行,结束行,起始列,结束列
            CellRangeAddress reportedAddress2 = new CellRangeAddress(16,18,3,3);//起始行,结束行,起始列,结束列
            CellRangeAddress reportedAddress3 = new CellRangeAddress(16,18,4,5);//起始行,结束行,起始列,结束列
            //被举报人职务和所在地区end

            //被举报人 职级start
            CellRangeAddress reportedLevel = new CellRangeAddress(19,21,0,0);//起始行,结束行,起始列,结束列
            CellRangeAddress reportedLevel1 = new CellRangeAddress(19,21,1,5);//起始行,结束行,起始列,结束列
            //被举报人 职级end

            //标题
            CellRangeAddress title = new CellRangeAddress(25,27,0,0);//起始行,结束行,起始列,结束列
            CellRangeAddress title1 = new CellRangeAddress(25,27,1,5);//起始行,结束行,起始列,结束列

            //问题类别
            CellRangeAddress question = new CellRangeAddress(28,31,0,0);//起始行,结束行,起始列,结束列
            CellRangeAddress question1 = new CellRangeAddress(28,31,1,5);//起始行,结束行,起始列,结束列

            //主要问题
            CellRangeAddress mainQuestion = new CellRangeAddress(32,37,0,0);//起始行,结束行,起始列,结束列
            CellRangeAddress mainQuestion1 = new CellRangeAddress(32,37,1,5);//起始行,结束行,起始列,结束列


            //2.创建工作表
            HSSFSheet sheet = workbook.createSheet("举报信息");
            //2.1加载合并单元格对象
            sheet.addMergedRegion(callRangeAddress);
            sheet.addMergedRegion(callRangeAddress1);
            sheet.addMergedRegion(callRangeAddress20);
            sheet.addMergedRegion(callRangeAddress21);
            sheet.addMergedRegion(callRangeAddress22);
            sheet.addMergedRegion(callRangeAddress23);

            sheet.addMergedRegion(callRangeAddress30);
            sheet.addMergedRegion(callRangeAddress31);
            sheet.addMergedRegion(callRangeAddress32);
            sheet.addMergedRegion(callRangeAddress33);

            sheet.addMergedRegion(callRangeAddress40);
            sheet.addMergedRegion(callRangeAddress41);
            sheet.addMergedRegion(callRangeAddress42);
            sheet.addMergedRegion(callRangeAddress43);

            //被举报人信息
            sheet.addMergedRegion(reported);
            sheet.addMergedRegion(reported1);
            sheet.addMergedRegion(reported2);
            sheet.addMergedRegion(reported3);

            sheet.addMergedRegion(reportedAddress);
            sheet.addMergedRegion(reportedAddress1);
            sheet.addMergedRegion(reportedAddress2);
            sheet.addMergedRegion(reportedAddress3);

            sheet.addMergedRegion(reportedLevel);
            sheet.addMergedRegion(reportedLevel1);

            //举报内容
            sheet.addMergedRegion(reportContent);
            //标题
            sheet.addMergedRegion(title);
            sheet.addMergedRegion(title1);
            //问题类别
            sheet.addMergedRegion(question);
            sheet.addMergedRegion(question1);
            //主要问题
            sheet.addMergedRegion(mainQuestion);
            sheet.addMergedRegion(mainQuestion1);

            //部项目经理部
            HSSFCellStyle headStyle = createCellStyle(workbook,(short)10,false,true);
            //派工单
            HSSFCellStyle erStyle = createCellStyle(workbook,(short)18,true,true);
            //班组和时间
            HSSFCellStyle sanStyle = createCellStyle(workbook,(short)10,false,false);
            //标题样式
            HSSFCellStyle colStyle = createCellStyle(workbook,(short)15,true,true);
            //内容样式
            HSSFCellStyle cellStyle = createCellStyle(workbook,(short)10,false,true);
            //设置默认列宽
            sheet.setDefaultColumnWidth(15);
            //3.创建行   举报人信息开始
            //3.1创建头标题行;并且设置头标题
            HSSFRow row = sheet.createRow(0); //行
            HSSFCell cell = row.createCell(0); ////创建一个单元格，第一列
            //加载单元格样式
            cell.setCellStyle(erStyle);
            cell.setCellValue("举报人信息");

            //创建标题行（被举报人（单位）信息）
            HSSFRow row1 = sheet.createRow(10); //行
            HSSFCell cell1 = row1.createCell(0); ////创建一个单元格，第一列
            //加载单元格样式
            cell1.setCellStyle(erStyle);
            cell1.setCellValue("被举报人(单位)信息");

            //举报内容
            HSSFRow content = sheet.createRow(22); //行
            HSSFCell contentCell = content.createCell(0); ////创建一个单元格，第一列
            //加载单元格样式
            contentCell.setCellStyle(erStyle);
            contentCell.setCellValue("举报内容");


            HSSFRow rowsan = sheet.createRow(1);  //创建一个行
            HSSFCell cellsan = rowsan.createCell(0); //创建一个单元格，第一列
            HSSFCell cellsan1 = rowsan.createCell(1); //创建一个单元格，第二列
            HSSFCell cellsan2 = rowsan.createCell(3);
            HSSFCell cellsan3 = rowsan.createCell(4);
            //加载单元格样式
            cellsan.setCellStyle(colStyle);
            cellsan.setCellValue("姓名");
            cellsan1.setCellStyle(colStyle);
            cellsan1.setCellValue(report.getName());
            cellsan2.setCellStyle(colStyle);
            cellsan2.setCellValue("身份证号");
            cellsan3.setCellStyle(colStyle);
            cellsan3.setCellValue(report.getCard());

            HSSFRow rowsanFace = sheet.createRow(4);  //创建一个行
            HSSFCell cellsanFace = rowsanFace.createCell(0); //创建一个单元格，第一列
            HSSFCell cellsanFace2 = rowsanFace.createCell(1); //创建一个单元格，第二列
            HSSFCell cellsanFace3 = rowsanFace.createCell(3);
            HSSFCell cellsanFace4 = rowsanFace.createCell(4);
            //加载单元格样式
            cellsanFace.setCellStyle(colStyle);
            cellsanFace.setCellValue("政治面貌");
            cellsanFace2.setCellStyle(colStyle);
            cellsanFace2.setCellValue(report.getPoliticalStatus());
            cellsanFace3.setCellStyle(colStyle);
            cellsanFace3.setCellValue("联系方式");
            cellsanFace4.setCellStyle(colStyle);
            cellsanFace4.setCellValue(report.getPhone());

            HSSFRow rowsanUnit = sheet.createRow(7);  //创建一个行
            HSSFCell cellsanUnit = rowsanUnit.createCell(0); //创建一个单元格，第一列
            HSSFCell cellsanUnit2 = rowsanUnit.createCell(1); //创建一个单元格，第二列
            HSSFCell cellsanUnit3 = rowsanUnit.createCell(3);
            HSSFCell cellsanUnit4 = rowsanUnit.createCell(4);
            //加载单元格样式
            cellsanUnit.setCellStyle(colStyle);
            cellsanUnit.setCellValue("单位或地址");
            cellsanUnit2.setCellStyle(colStyle);
            cellsanUnit2.setCellValue(report.getWorkUnit());
            cellsanUnit3.setCellStyle(colStyle);
            cellsanUnit3.setCellValue("级别");
            cellsanUnit4.setCellStyle(colStyle);
            cellsanUnit4.setCellValue(report.getJibie());

            //举报人信息结束

            //被举报人信息开始
            HSSFRow reportedUnit = sheet.createRow(13);  //创建一个行
            HSSFCell reportedCellsan = reportedUnit.createCell(0); //创建一个单元格，第一列
            HSSFCell reportedCellsan2 = reportedUnit.createCell(1); //创建一个单元格，第二列
            HSSFCell reportedCellsan3 = reportedUnit.createCell(3);
            HSSFCell reportedCellsan4 = reportedUnit.createCell(4);
            //加载单元格样式
            reportedCellsan.setCellStyle(colStyle);
            reportedCellsan.setCellValue("姓名");
            reportedCellsan2.setCellStyle(colStyle);
            reportedCellsan2.setCellValue(report.getNameTwo());
            reportedCellsan3.setCellStyle(colStyle);
            reportedCellsan3.setCellValue("单位");
            reportedCellsan4.setCellStyle(colStyle);
            reportedCellsan4.setCellValue(report.getWorkUnitTwo());

            //职务和所在地区
            HSSFRow reportedAdd = sheet.createRow(16);  //创建一个行
            HSSFCell reportedAddressCellsan = reportedAdd.createCell(0); //创建一个单元格，第一列
            HSSFCell reportedAddressCellsan2 = reportedAdd.createCell(1); //创建一个单元格，第二列
            HSSFCell reportedAddressCellsan3 = reportedAdd.createCell(3);
            HSSFCell reportedAddressCellsan4 = reportedAdd.createCell(4);
            //加载单元格样式
            reportedAddressCellsan.setCellStyle(colStyle);
            reportedAddressCellsan.setCellValue("职务");
            reportedAddressCellsan2.setCellStyle(colStyle);
            reportedAddressCellsan2.setCellValue(report.getPositionTwo());
            reportedAddressCellsan3.setCellStyle(colStyle);
            reportedAddressCellsan3.setCellValue("所在地区");
            reportedAddressCellsan4.setCellStyle(colStyle);
            reportedAddressCellsan4.setCellValue(report.getAreaTwo());
            //职级
            //职务和所在地区
            HSSFRow reportedLevelRow = sheet.createRow(19);  //创建一个行
            HSSFCell reportedLevelCellsan = reportedLevelRow.createCell(0); //创建一个单元格，第一列
            HSSFCell reportedLevelCellsan2 = reportedLevelRow.createCell(1); //创建一个单元格，第二列
            //加载单元格样式
            reportedLevelCellsan.setCellStyle(colStyle);
            reportedLevelCellsan.setCellValue("职级");
            reportedLevelCellsan2.setCellStyle(colStyle);
            reportedLevelCellsan2.setCellValue(report.getLevel());

            //标题
            HSSFRow reportTitleRow = sheet.createRow(25);  //创建一个行
            HSSFCell reportTitleCellsan = reportTitleRow.createCell(0); //创建一个单元格，第一列
            HSSFCell reportTitleCellsan1 = reportTitleRow.createCell(1); //创建一个单元格，第二列
            //加载单元格样式
            reportTitleCellsan.setCellStyle(colStyle);
            reportTitleCellsan.setCellValue("标题(最多50字)");
            reportTitleCellsan1.setCellStyle(colStyle);
            reportTitleCellsan1.setCellValue(report.getTitle());

            //问题类别
            HSSFRow reportQuestionRow = sheet.createRow(28);  //创建一个行
            HSSFCell reportQuestionCellsan = reportQuestionRow.createCell(0); //创建一个单元格，第一列
            HSSFCell reportQuestionCellsan1 = reportQuestionRow.createCell(1); //创建一个单元格，第二列
            //加载单元格样式
            reportQuestionCellsan.setCellStyle(colStyle);
            reportQuestionCellsan.setCellValue("问题类别");
            reportQuestionCellsan1.setCellStyle(colStyle);
            reportQuestionCellsan1.setCellValue(report.getProblemCategory());

            //主要问题
            HSSFRow mainQuestionRow = sheet.createRow(32);  //创建一个行
            HSSFCell mainQuestionCellsan = mainQuestionRow.createCell(0); //创建一个单元格，第一列
            HSSFCell mainQuestionCellsan1 = mainQuestionRow.createCell(1); //创建一个单元格，第二列
            //加载单元格样式
            mainQuestionCellsan.setCellStyle(colStyle);
            mainQuestionCellsan.setCellValue("主要问题(最多200字)");
            mainQuestionCellsan1.setCellStyle(colStyle);
            mainQuestionCellsan1.setCellValue(report.getMainQuestion());
            //5.输出
            workbook.write(fout);
            fout.close();
            out.close();
        }catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    /**
     *
     * @param workbook
     * @param fontsize
     * @return 单元格样式
     *  HSSFCellStyle colStyle = createCellStyle(workbook,(short)15,true,true);
     */
    public static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize, boolean flag, boolean flag1) {
        // TODO Auto-generated method stub
        HSSFCellStyle style = workbook.createCellStyle();
        //是否水平居中
        if(flag1){
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        }

        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        //创建字体
        HSSFFont font = workbook.createFont();
        //是否加粗字体
        if(flag){
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        }
        font.setFontHeightInPoints(fontsize);
        //加载字体
        style.setFont(font);
        return style;
    }
}

